﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Base.Common;
using System.Data.SqlClient;

namespace Base.DataAccess.Common
{
    public class MenuDAL
    {
        private SQLHelper sqHelper = new SQLHelper();

        public DataTable GetAllMenu(string Language)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(1);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;
            return sqHelper.ExecuteDataTable("SP_Com_Menu", true, parameters);
        }

        public DataSet GetMenu(string Language, int PageSize, int PageIndex, string Sort, string Dir, Guid ParentId)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@ParentId";
            parameters[1].Value = ParentId;

            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat(" select menu.*,value.Value as MenuName");
            sbSql.AppendFormat(" from Com_Menu menu");
            sbSql.AppendFormat(" left join Com_Value value on menu.NameId=value.Id");
            sbSql.AppendFormat(" left join Com_Language lang on value.LanguageId=lang.Id and lang.Name=@Language WHERE IsDelete =0 AND ParentId=@ParentId");
            return sqHelper.ExecuteDataSet(sbSql.ToString(), false, PageSize, PageIndex, Sort, Dir, parameters);
        }


        public bool AddMenu(string Language, string Name, string LinkURL, Guid ParentId, string Description, int IsMenu, int IsDisplay)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(7);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.String;
            parameters[1].ParameterName = "@Name";
            parameters[1].Value = Name;

            parameters[2] = (SqlParameter)sqHelper.GetParameter();
            parameters[2].DbType = DbType.String;
            parameters[2].ParameterName = "@LinkURL";
            parameters[2].Value = LinkURL;

            parameters[3] = (SqlParameter)sqHelper.GetParameter();
            parameters[3].DbType = DbType.Guid;
            parameters[3].ParameterName = "@ParentId";
            parameters[3].Value = ParentId;

            parameters[4] = (SqlParameter)sqHelper.GetParameter();
            parameters[4].DbType = DbType.String;
            parameters[4].ParameterName = "@Description";
            parameters[4].Value = Description;

            parameters[5] = (SqlParameter)sqHelper.GetParameter();
            parameters[5].DbType = DbType.Int32;
            parameters[5].ParameterName = "@IsMenu";
            parameters[5].Value = IsMenu;

            parameters[6] = (SqlParameter)sqHelper.GetParameter();
            parameters[6].DbType = DbType.Int32;
            parameters[6].ParameterName = "@IsDisplay";
            parameters[6].Value = IsDisplay;

            int iResult = ConvertHelper.GetInt(sqHelper.ExecuteValue("SP_Com_AddMenu", true, parameters));
            if (iResult >= 0) return true;
            else return false;
        }

        public void MoveMenu(Guid Id, int Type)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);

            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@Id";
            parameters[0].Value = Id;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Int32;
            parameters[1].ParameterName = "@Type";
            parameters[1].Value = Type;


            sqHelper.ExecuteValue("SP_Com_UpdateMenuSort", true, parameters);

        }

        public int DeleteMenu(Guid Id)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(1);

            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.Guid;
            parameters[0].ParameterName = "@Id";
            parameters[0].Value = Id;

            return ConvertHelper.GetInt(sqHelper.ExecuteCommand("SP_Com_DeleteMenu", true, parameters));
        }

        public DataTable GetMenuById(Guid Id, string Language)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(2);

            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@Id";
            parameters[1].Value = Id;


            return sqHelper.ExecuteDataTable("SP_Com_GetMenuById", true, parameters);

        }

        public bool UpdateMenu(string Language, Guid Id, string Name, string LinkURL, string Description, int IsMenu, int IsDisplay)
        {
            SqlParameter[] parameters = (SqlParameter[])sqHelper.GetParameters(7);
            parameters[0] = (SqlParameter)sqHelper.GetParameter();
            parameters[0].DbType = DbType.String;
            parameters[0].ParameterName = "@Language";
            parameters[0].Value = Language;

            parameters[1] = (SqlParameter)sqHelper.GetParameter();
            parameters[1].DbType = DbType.Guid;
            parameters[1].ParameterName = "@Id";
            parameters[1].Value = Id;

            parameters[2] = (SqlParameter)sqHelper.GetParameter();
            parameters[2].DbType = DbType.String;
            parameters[2].ParameterName = "@Name";
            parameters[2].Value = Name;

            parameters[3] = (SqlParameter)sqHelper.GetParameter();
            parameters[3].DbType = DbType.String;
            parameters[3].ParameterName = "@LinkURL";
            parameters[3].Value = LinkURL;

            parameters[4] = (SqlParameter)sqHelper.GetParameter();
            parameters[4].DbType = DbType.String;
            parameters[4].ParameterName = "@Description";
            parameters[4].Value = Description;

            parameters[5] = (SqlParameter)sqHelper.GetParameter();
            parameters[5].DbType = DbType.Int32;
            parameters[5].ParameterName = "@IsMenu";
            parameters[5].Value = IsMenu;

            parameters[6] = (SqlParameter)sqHelper.GetParameter();
            parameters[6].DbType = DbType.Int32;
            parameters[6].ParameterName = "@IsDisplay";
            parameters[6].Value = IsDisplay;

            int result = sqHelper.ExecuteCommand("SP_Com_UpdateMenu", true, parameters);
            if (result > 0) return true;
            else return false;
        }
    }
}
